﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;

namespace SoftHotel.Models
{
    public class DCDAO
    {
        Conexion conexionBD = new Conexion();
        public SelectListItem[] obtenerOCs()
        {
            List<SelectListItem> ls = new List<SelectListItem>();
            string query = "select * from OrdenCompra";
            SqlDataReader dr = conexionBD.EjecutarReader(query);
            while (dr != null && dr.Read())
            {
                ls.Add(new SelectListItem { Value = dr[0].ToString(), Text = dr[0].ToString() });
            }
            SelectListItem[] sls = new SelectListItem[ls.Count];
            for (int i = 0; i < ls.Count; i++)
            {
                sls[i] = ls[i];
            }
            return sls;
        }

        public SelectListItem[] ObtenerTipos(string tipo)
        {
            //conexionBD.AbrirConexion();
            List<SelectListItem> ls = new List<SelectListItem>();
            string query = "select valor from Parametro where tipo = '" + tipo + "'";
            SqlDataReader dr = conexionBD.EjecutarReader(query);
            while (dr != null && dr.Read())
            {
                ls.Add(new SelectListItem { Value = dr[0].ToString(), Text = dr[0].ToString() });
            }
            SelectListItem[] sls = new SelectListItem[ls.Count];
            for (int i = 0; i < ls.Count; i++)
            {
                sls[i] = ls[i];
            }
            return sls;
        }

        public int Guardar(DocumentoCompra dc)
        {
            string idOC = dc.idOC;
            string tipoDoc = dc.tipo;
            string idProveedor = "";
            string idSede = "";
            string idMoneda = "";
            string idFormaPago = "";
            string montoTotal = "";
            DateTime now1 = DateTime.Now;
            string now = String.Format("{0:d/M/yyyy HH:mm:ss}", now1);
            string query = "select * from OrdenCompra where idOrdenCompra = '" + idOC + "'";
            SqlDataReader dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
            {
                idProveedor = dr[1].ToString();
                idSede = dr[2].ToString();
                idMoneda = dr[5].ToString();
                idFormaPago = dr[6].ToString();
                montoTotal = dr[9].ToString();
            }
            dr.Close();
            query = "select idParametro from Parametro where tipo = 'TIPO_DC' and valor =  '" + tipoDoc + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                tipoDoc = dr[0].ToString();
            dr.Close();
            query = "insert into DocumentoCompra (idOrdenCompra,idSede,fechaEmision,fechaPago,moneda,tipo,formaPago,estado,montoTotal) " +
                    "values ('" + idOC + "','" + idSede + "','" + now + "', NULL,'" + idMoneda +
                    "', '" + tipoDoc + "','"  + idFormaPago + "','124' ,'"  + montoTotal + "')";
            conexionBD.EjecutarComando(query);
            query = "select TOP(1) idDocumentoCompra from DocumentoCompra order by idOrdenCompra desc";
            dr = conexionBD.EjecutarReader(query);
            int docc = 0;

            if (dr != null && dr.Read())
                docc = Int32.Parse(dr[0].ToString());
            dr.Close();
            return docc;
        }

        internal DocumentoCompra buscarByID(string id)
        {
            DocumentoCompra doccompra = new DocumentoCompra();
            string idOC = "";
            string tipoDoc = "";
            string idProveedor = "";
            string idSede = "";
            string idMoneda = "";
            string idFormaPago = "";
            string montoTotal = "";
            string query = "select * from DocumentoCompra where idDocumentoCompra = '" + id + "'";
            SqlDataReader dr = conexionBD.EjecutarReader(query);
            
            if (dr != null && dr.Read())
            {
                doccompra.idDocCompra = dr[0].ToString();
                doccompra.idOC= dr[1].ToString();
                doccompra.idSede = dr[2].ToString();
                doccompra.fechaEmision = dr[3].ToString();
                doccompra.fechaPago = dr[4].ToString();
                doccompra.moneda = dr[5].ToString();
                doccompra.tipo = dr[6].ToString();
                doccompra.formapago = dr[7].ToString();
                doccompra.estado= dr[8].ToString();
                doccompra.montoTotal = dr[9].ToString();
            }
            dr.Close();
            query = "select valor from Parametro where  idParametro =  '" + doccompra.moneda + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.moneda = dr[0].ToString();
            dr.Close();
            query = "select idProveedor from OrdenCompra where  idOrdenCompra =  '" + doccompra.idOC+ "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.Proveedor = dr[0].ToString();
            dr.Close();
            query = "select razonSocial from Proveedor where  idProveedor =  '" + doccompra.Proveedor + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.Proveedor = dr[0].ToString();
            dr.Close();
            query = "select nombre from Sede where  idSede =  '" + doccompra.idSede + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.idSede = dr[0].ToString();
            dr.Close();
            query = "select valor from Parametro where  idParametro =  '" + doccompra.tipo + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.tipo = dr[0].ToString();
            dr.Close();
            query = "select valor from Parametro where  idParametro =  '" + doccompra.formapago + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.formapago = dr[0].ToString();
            dr.Close();
            query = "select valor from Parametro where  idParametro =  '" + doccompra.estado + "'";
            dr = conexionBD.EjecutarReader(query);
            if (dr != null && dr.Read())
                doccompra.estado = dr[0].ToString();
            dr.Close();

            return doccompra;
        }
    }
}